package com.lagou.zuoye.dao;

import com.lagou.zuoye.bean.Student;
import com.lagou.zuoye.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImp implements StudentDao {
    //自定义成员方法实现向数据库添加数据
    @Override
    public int addStudent(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.获取连接
            connection = DbUtil.getConnection();
            //2.编写sql
            String sql = "insert into t_student(s_id,s_name,s_sex,s_birthday,s_email,s_notes) values(?,?,?,?,?,?)";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,null);
            preparedStatement.setString(2,student.getS_name());
            preparedStatement.setString(3,student.getS_sex());
            preparedStatement.setString(4,student.getS_birthday());
            preparedStatement.setString(5,student.getS_email());
            preparedStatement.setString(6,student.getS_notes());
            int i = preparedStatement.executeUpdate();
            if(i!=0){
                System.out.println("添加学员成功");
                return i;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;   //当i的值为0时，表示添加失败
    }

    /**
     * 自定义成员方法实现对数据库里数据的删除
     * @param name
     * @return
     */
    @Override
    public int deleteStudent(String name) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtil.getConnection();
            String sql = "delete from t_student where s_name = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            int i = preparedStatement.executeUpdate();
            if(i!=0){
                System.out.println("删除学员成功");
                return i;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;   //当i的值为0时，表示删除失败
    }

    /**
     * 自定义成员方法实现对数据库数据的修改
     * @param student
     * @param name
     * @return
     */
    @Override
    public int alterStudent(Student student,String name) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtil.getConnection();
            String sql = "update t_student set s_name = ?,s_sex =?,s_birthday = ?,s_email = ?,s_notes = ? where s_name = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getS_name());
            preparedStatement.setString(2,student.getS_sex());
            preparedStatement.setString(3,student.getS_birthday());
            preparedStatement.setString(4,student.getS_email());
            preparedStatement.setString(5,student.getS_notes());
            preparedStatement.setString(6,name);
            int i = preparedStatement.executeUpdate();
            if(i!=0){
                System.out.println("修改学员成功");
                return i;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;   //当i的值为0时，表示修改失败
    }

    /**
     * 自定义成员方法实现对数据库数据的查询
     * @param name
     * @return
     */
    @Override
    public Student selectStudent(String name) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DbUtil.getConnection();
            String sql = "select * from t_student where s_name = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Student student = new Student(resultSet.getString("s_id"),
                                              resultSet.getString("s_name"),
                                              resultSet.getString("s_sex"),
                                              resultSet.getString("s_birthday"),
                                              resultSet.getString("s_email"),
                                              resultSet.getString("s_notes"));
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;    //当返回null值时，表示查询失败，没有查询到信息
    }

    /**
     * 自定义成员方法实现查询数据库的所有信息，并打印在页面上
     * @return
     */
    @Override
    public List<Student> viewAllStudent() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            List<Student> listStudent = new ArrayList<>();
            connection = DbUtil.getConnection();
            String sql = "select * from t_student";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                listStudent.add(new Student(resultSet.getString("s_id"),
                                            resultSet.getString("s_name"),
                                            resultSet.getString("s_sex"),
                                            resultSet.getString("s_birthday"),
                                            resultSet.getString("s_email"),
                                            resultSet.getString("s_notes")));
            }
            System.out.println(listStudent);
            System.out.println(listStudent.get(0));
            System.out.println(listStudent.get(1));
            return listStudent;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}
